package com.szjz.excel.importexport;

import com.szjz.excel.importexport.annotation.ImportMapping;
import com.szjz.excel.importexport.annotation.SheetMapping;
import com.szjz.excel.importexport.exception.ImportExportException;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.*;

/**
 * 支持2003、2007<br>
 * 模型pojo, 需提供默认构造函数, 且属性存在setter方法
 */
public class ExcelReader {

    private static final Logger logger = LoggerFactory.getLogger(ExcelReader.class);

    private Workbook workbook;

    public static ExcelReader build(File file) throws IOException {
        if (file == null) {
            throw new ImportExportException("文件file不能为空");
        }

        return build(new FileInputStream(file));
    }

    public static ExcelReader build(InputStream in) throws IOException {
        if (in == null) {
            throw new ImportExportException("输入流in不能为空");
        }

        ExcelReader excelReader = new ExcelReader();
        try {
            excelReader.workbook = new XSSFWorkbook(in); // 解析2007
        } catch (Exception e) {
            excelReader.workbook = new HSSFWorkbook(in); // 解析2003
        } finally {
            in.close();
        }

        return excelReader;
    }

    public <T> List<T> read(Class<T> clazz) throws ImportExportException {
        // 读取sheetName或sheetNo
        SheetMapping annotation = clazz.getDeclaredAnnotation(SheetMapping.class);
        String sheetName = annotation.sheetName();
        int sheetNo = annotation.sheetNo();
        if (StringUtils.isBlank(sheetName) && sheetNo == -1) {
            throw new ImportExportException("请指定sheetName或sheetNo");
        }
        if (StringUtils.isNotBlank(sheetName) && sheetNo >= 0) {
            throw new ImportExportException("请不要同时指定sheetName和sheetNo");
        }

        if (StringUtils.isNotBlank(sheetName)) {
            return this.read(sheetName, clazz);
        } else {
            return this.read(sheetNo, clazz);
        }
    }

    /**
     * @param sheetNo sheet编号, 从0开始
     * @param clazz   反射对象的Class
     * @return
     * @throws ImportExportException
     */
    private <T> List<T> read(int sheetNo, Class<T> clazz) throws ImportExportException {
        if (sheetNo > this.workbook.getNumberOfSheets() - 1) {
            throw new ImportExportException("sheet不存在(从0开始), 序号: {}", sheetNo);
        }

        Sheet sheet = this.workbook.getSheetAt(sheetNo);
        return buildRowsObject(sheet, clazz);
    }

    /**
     * @param sheetName sheet名称
     * @param clazz     反射对象的Class
     * @return
     * @throws ImportExportException
     */
    private <T> List<T> read(String sheetName, Class<T> clazz) throws ImportExportException {
        Sheet sheet = this.workbook.getSheet(sheetName);
        if (sheet == null) {
            throw new ImportExportException("sheet\"{}\"不存在", sheetName);
        }
        return buildRowsObject(sheet, clazz);
    }

    /**
     * 构建List集合
     *
     * @param sheet
     * @param clazz 反射对象的Class
     * @return
     * @throws ImportExportException
     */
    private <T> List<T> buildRowsObject(Sheet sheet, Class<T> clazz) throws ImportExportException {
        // sheet名称
        String sheetName = sheet.getSheetName();
        // 模型字段信息
        Map<String, ImportFieldInfo> fieldInfoMap = FieldInfoUtil.getImportFieldInfo(clazz);

        // 构建clazz字段和excel列的对应关系
        String[] cellMappings = new String[fieldInfoMap.size()];
        Row headerRow = sheet.getRow(0);
        for (Iterator<Map.Entry<String, ImportFieldInfo>> it = fieldInfoMap.entrySet().iterator(); it.hasNext(); ) {
            Map.Entry<String, ImportFieldInfo> entry = it.next();
            String fieldName = entry.getKey();
            String headName = entry.getValue().getFieldAnnotation().headName();

            int mappingNum = -1;
            for (int cellNum = 0; cellNum < headerRow.getLastCellNum(); cellNum++) {
                Cell cell = headerRow.getCell(cellNum);
                if (cell == null) {// 不允许中间有空的列
                    break;
                }
                String cellValue = getValue(cell);
                if (cellValue.equals(headName)) {
                    mappingNum = cellNum;
                }
            }
            if (mappingNum == -1) {// 映射失败
                logger.info("\"{}\"缺少\"{}\"列", sheetName, headName);
                throw new ImportExportException("\"{}\"缺少\"{}\"列", sheetName, headName);
            }
            cellMappings[mappingNum] = fieldName;
        }

        List<T> resultList = new ArrayList<T>();
        int lastRowNum = sheet.getLastRowNum();
        for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) { // 一个row就相当于一个Object
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                // 空行表示结束
                return resultList;
            }
            // 判断是否整行的值都为空
            boolean hasValue = false;
            for (int cellNum = 0; cellNum < cellMappings.length; cellNum++) {// 读取映射的列
                Cell cell = row.getCell(cellNum);
                if (cell != null && StringUtils.isNotBlank(getValue(cell))) {
                    hasValue = true;
                }
            }
            if (!hasValue) {
                // 空行表示结束
                return resultList;
            }

            T t = buildObject(sheetName, row, cellMappings, fieldInfoMap, clazz);
            resultList.add(t);
        }

        return resultList;
    }

    /**
     * 根据把excel行构建Object对象
     *
     * @param sheetName
     * @param row          excel行
     * @param cellMappings excel列序号和字段的映射关系
     * @param fieldInfoMap clazz需要匹配的字段, 及对应的注解和setter方法
     * @param clazz        反射对象的Class
     * @return
     * @throws ImportExportException
     */
    private <T> T buildObject(String sheetName,
                              Row row,
                              String[] cellMappings,
                              Map<String, ImportFieldInfo> fieldInfoMap,
                              Class<T> clazz) throws ImportExportException {
        T object;
        try {
            object = clazz.newInstance();
        } catch (InstantiationException | IllegalAccessException e) {
            logger.error("模型" + clazz.getSimpleName() + "实例化失败", e);
            throw new ImportExportException("模型" + clazz.getSimpleName() + "实例化失败");
        }

        for (int cellNum = 0; cellNum < cellMappings.length; cellNum++) {// 读取映射的列
            String property = cellMappings[cellNum];
            ImportFieldInfo fieldInfo = fieldInfoMap.get(property);

            String cellValue = null;
            Cell cell = row.getCell(cellNum);
            if (cell != null) {
                cellValue = getValue(cell);
            }
            if (StringUtils.isBlank(cellValue)) {
                logger.info("\"{}\"第{}行第{}列无数据", sheetName, row.getRowNum() + 1, cellNum + 1);
                if (fieldInfo.getFieldAnnotation().required()) {
                    throw new ImportExportException("\"{}\"第{}行第{}列无数据", sheetName, row.getRowNum() + 1, cellNum + 1);
                }
                continue;
            }
            String prefixTips = "\"" + sheetName + "\"第" + (row.getRowNum() + 1) + "行第" + (cellNum + 1) + "列, ";
            setObjectPropertyValue(object, fieldInfo, cellValue, prefixTips);
        }
        return object;
    }

    /**
     * 设置对象的字段值
     *
     * @param object     需要构建的对象
     * @param fieldInfo
     * @param value      需要设置的值
     * @param prefixTips 错误提示前缀
     * @return
     * @throws ImportExportException 设置值失败时抛出
     */
    private void setObjectPropertyValue(Object object,
                                        ImportFieldInfo fieldInfo,
                                        String value,
                                        String prefixTips) throws ImportExportException {
        Field field = fieldInfo.getField();
        Method setter = fieldInfo.getSetter();
        ImportMapping fieldAnnotation = fieldInfo.getFieldAnnotation();

        Object[] values = new Object[1];

        String typeName = field.getType().getName();
        String typeSimpleName = field.getType().getSimpleName();
        String typeChineseName = typeSimpleName;

        // 数字
        try {
            if ("java.lang.Short".equals(typeName) || "short".equals(typeName)) {
                typeChineseName = "整数(short)";
                values[0] = Short.valueOf(value);
            } else if ("java.lang.Integer".equals(typeName) || "int".equals(typeName)) {
                typeChineseName = "整数(int)";
                values[0] = Integer.valueOf(value);
            } else if ("java.lang.Long".equals(typeName) || "long".equals(typeName)) {
                typeChineseName = "整数(long)";
                values[0] = Long.valueOf(value);
            } else if ("java.lang.Float".equals(typeName) || "float".equals(typeName)) {
                typeChineseName = "浮点数(float)";
                values[0] = Float.valueOf(value);
            } else if ("java.lang.Double".equals(typeName) || "double".equals(typeName)) {
                typeChineseName = "浮点数(double)";
                values[0] = Double.valueOf(value);
            } else if ("java.math.BigDecimal".equals(typeName)) {
                typeChineseName = "浮点数(BigDecimal)";
                values[0] = new BigDecimal(value);
            }
        } catch (NumberFormatException e) {
            throw new ImportExportException(prefixTips
                    + "{}({})解析错误, 输入: {}, 期望类型: {}", fieldInfo.getFieldAnnotation().headName(), field.getName(), value, typeChineseName);
        }

        if ("java.lang.String".equals(typeName) || "String".equals(typeName)) {
            typeChineseName = "字符串(String)";
            values[0] = value;
        } else if ("java.lang.Boolean".equals(typeName) || "boolean".equals(typeName)) {
            typeChineseName = "布尔值(true/false)";
            values[0] = Boolean.valueOf(value);
        } else if ("java.util.Date".equals(typeName) || "java.sql.Timestamp".equals(typeName)) {
            typeChineseName = "日期(" + typeSimpleName + ")";
            String format = fieldAnnotation.format();
            if (StringUtils.isBlank(format)) {
                throw new ImportExportException(prefixTips + "模型 " + field.getName() + " 字段未设置日期格式");
            }
            Date date = null;
            try {
                date = DateUtils.parseDate(value, format);
            } catch (ParseException e) {
                throw new ImportExportException(prefixTips + "模型 " + field.getName() + " 字段解析出错, 输入:" + value + ", 期望格式:"
                        + format);
            }

            if ("java.util.Date".equals(typeName)) {
                values[0] = date;
            } else if ("java.sql.Timestamp".equals(typeName)) {
                values[0] = new Timestamp(date.getTime());
            }
        }
        // 调用setter
        try {
            setter.invoke(object, values);
        } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
            logger.error("模型" + object.getClass().getSimpleName() + "设置" + field.getName() + "值失败", e);
            throw new ImportExportException(prefixTips + "模型" + object.getClass().getSimpleName() + "设置" + field.getName()
                    + "值失败");
        }
    }

    private String getValue(Cell cell) {
        if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            return NumberToTextConverter.toText(cell.getNumericCellValue());
        } else {
            return String.valueOf(cell.getStringCellValue());
        }
    }

}
